﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Thaire.Investment.BL
{
    public class R3Query
    {
        public static string r3100header = @"
select
'' as Item_1
,0.0 as Income_DuringYear_Amount_2
,0.0 as Income_Receivable_Related_Amont_3
,0.0 as Income_Receivable_NonRelated_Amont_4
,0.0 as Income_Receivable_Total_Amont_5 -- formula =(Amount_3+Amount_4)
,0.0 as Income_Receivable_Assessment_Appraiser_Amont_6 -- formula =(Amount_5)
,0.0 as Income_Receivable_Assessment_NotAppraiser_Amont_7
,0.0 as Income_Receivable_Assessment_Total_Amont_8 -- formula =(Amount_6+Amount_7)
,0.0 as Asset_Section27_4_9
,'' as RemarkOIC_10
from FIXINCOMESECURITIES_HEADER
";


        public static string r3200header = @"
select
'' as Item_1
,0.0 as Profit_Disposal_Amont_2
,0.0 as Profit_Transfe_Amontr_3
,0.0 as Loss_Impairment_4
,0.0 as Profit_Account_Imp_Amont_5
,0.0 as Profit_Revaluation_Amont_6
from FIXINCOMESECURITIES_HEADER
";


        public static string r3300header = @"
select
'' as AssetClass_h1
,'' as AssetType_h2
,'' as Related_h3
,0.0 as Securities_Trade_Amount_2
,0.0 as Securities_ForSale_Amount_3
,0.0 as Debt_Securities_Hold_Due_Amount_4
,0.0 as Securities_General_Amount_5
,0.0 as Securities_Total_Amount_6 -- formula =(Amount_2+Amount_3+Amount_4+Amount_5)
,0.0 as Percent_Securitie_Per_Investment_7
,0.0 as Estimate_Securities_Trade_Amount_8
,0.0 as Estimate_Securities_ForSale_Amount_9
,0.0 as Estimate_Debt_Securities_Hold_Due_Amount_10
,0.0 as Estimate_Securities_General_Amount_11
,0.0 as Estimate_Securities_Total_Amount_12 -- formula =(Amount_8+Amount_9+Amount_10+Amount_11)
,0.0 as Percent_Estimate_Securitie_Per_Investment_13
from FIXINCOMESECURITIES_HEADER
";


        public static string r3301header = @"
select
'' as AssetClass_h1
,'' as AssetType_h2
,'' as Related_h3
,0.0 as Book_Current_1Year_Amount_2
,0.0 as Book_Current_More1Year_Less5Year_Amount_3
,0.0 as Book_Current_More5Year_Less10Year_Amount_4
,0.0 as Book_Current_More1Year_Less5Year_Amount_5
,0.0 as Book_Current_OverDueDate_Amount_6 -- formula =(Amount_2+Amount_3+Amount_4+Amount_5)
,0.0 as Book_Sum_Amount_7 --=sum(2-6)
,0.0 as Book_Current_AFR_Amount_8
,0.0 as Book_Current_Net_Amount_9 --=sum(7-8)
,0.0 as Book_LastYear_Amount_10
from FIXINCOMESECURITIES_HEADER
";




         
        public static string r3302header = @"

select 10 ISSUER_TYPE_ID,  N'1. พันธบัตรรัฐบาลไทย' ISSUE_TYPE union
--select 11 ISSUER_TYPE_ID, N'2. พันธบัตรองค์การหรือรัฐวิสาหกิจ' ISSUE_TYPE union
select 12 ISSUER_TYPE_ID, N'2. พันธบัตรองค์การหรือรัฐวิสาหกิจ' ISSUE_TYPE union
 select  13 ISSUER_TYPE_ID, N'3. ตั๋วเงินคลัง' ISSUE_TYPE union
select 14 ISSUER_TYPE_ID, N'4. หลักทรัพย์ต่างประเทศ'
";

        public static string r3302detail = @"



select 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
,'' FIXED_INCOME_FULLNAME
,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
,FIXINCOMESECURITIES_HEADER.ISSUER_ID
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME +'('+FIXINCOMESECURITIES_HEADER.SYMBOL+')' AS ISSUER_NAME
,FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_NAME
,FIXINCOMESECURITIES_HEADER.NUMBER_TIME	
,FIXINCOMESECURITIES_HEADER.ISSUE_DATE 
,0.0 AS PHYSICAL_NUMBER
,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE
,FIXINCOMESECURITIES_HEADER.MATURITY_DATE		
,0.0 AS PHYSICAL_AMOUNT
,0.0 AS INTEREST_RATE 
,0.0 AS AMOUNT_7
,FIXINCOMESECURITIES_PURCHASE.PURCHASE_VALUE_TH AS NET_AMOUNT_8
,0.0 AS AMOUNT_9
,0.0 AS AMOUNT_10
,0.0 AS AMOUNT_11
,0.0 AS AMOUNT_12
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE AS AMOUNT_13
,0.0 AS AMOUNT_14 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION = 2) AS AMOUNT_15 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and (select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19') AS AMOUNT_16 

, case ( select 
	top 1  (select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)	
    from 
    FIXINCOMESECURITIES_TRANSACTION
    where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	     
  ) 
  when  'เงินสำรอง' then  
		FIXINCOMESECURITIES_PURCHASE.PAR * 
		ISNULL(
			 (select	
			 sum(FIXINCOMESECURITIES_PURCHASE2.UNIT) 
			from dbo.FIXINCOMESECURITIES_PURCHASE AS FIXINCOMESECURITIES_PURCHASE2 inner join dbo.FIXINCOMESECURITIES_TRANSACTION AS FIXINCOMESECURITIES_TRANSACTION2 on  FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_PURCHASE_ID = FIXINCOMESECURITIES_TRANSACTION2.PURCHASE_ID
			where 
				FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_HEADER_ID
				and ((select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION2.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)= 'เงินสำรอง')
   
		   )
		  ,0)
  else
		case FIXINCOMESECURITIES_PURCHASE.KEEP_AT 
		when 'Custodian' then   
			
			FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE
			*
			(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
				where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
				and 
				(select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19'
		    )
			*
			(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_PURCHASE.KEEP_AT = 'Custodian')
			

		else 0
		end	
  end as AMOUNT_17

,FIXINCOMESECURITIES_PURCHASE.REMARK_OIC	
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME
,'' AS FIXED_INCOME_FULLNAME_TH 
,FIXINCOMESECURITIES_HEADER.SYMBOL
, '' AS Notes
, 0.0 AS Unit
, 0.0 AS Amortize_Interest 
, 0.0 AS Defered_Interest 
, 0.0 AS Unrealised_gains_per_loss
, 0.0 AS Market_Yield
, 0.0 AS Cost_Yield
, 0.0 AS Modified_duration
, getdate() AS Coupon_payment_date
, 0.0 AS Mkt_value_plus_Mduration
, '' AS Tier1
, '' AS Tier2
, '' AS Par_plus_Market_Yield
, '' AS duration_year_from_Settle_date
, '' AS duration_year_from_EOM
, '' AS IRR
, '' AS Clean_plus_cost_yield
, '' AS Mkt_pric_plus_Mkt_yield


,FIXINCOMESECURITIES_HEADER.GUARANTOR_NAME 
,(SELECT RATING_HISTORY.ISSUER_RATING FROM RATING_HISTORY where RATING_HISTORY.FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID 
  and RATING_HISTORY.FINANCIAL_INSTRUMENT_TYPE  = 'ISSUER'
  )as RATING


from FIXINCOMESECURITIES_HEADER
inner join dbo.FIXINCOMESECURITIES_PURCHASE on 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
where FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID is not null
";


      

       public static string r3305qry3 = @"
        select
'' as AssetClass_h1
,'' as AssetType_h2
,'' as Related_h3
,'' as INVOLVED_PARTY_TYPE
,'' as ISSUER_1
,'' as Purchase_Date_2
,0.0 as Unit_Amount_3
,0.0 as Percent_Unit_Per_Total_4
,0.0 as Par_Amount_5
,0.0 as Paid_Amount_6
,0.0 as Announce_Booking_Amount_7
,0.0 as Purchase_Amount_8
,0.0 as Book_DuringYear_Amount_9
,0.0 as Book_Appraisal_Amount_10
,0.0 as Book_Net_Amount_11
,0.0 as BV_Amount_12
,0.0 as Quatation_Amount_13
,0.0 as Quatation_BV_Ratio_14
,0.0 as Estimate_Amount_15
,0.0 as Obligation_Amount_16
,0.0 as Asset_Section27_4_17
,'' as Remark_OIC_18
,0.0 as CostPrice_19
,0.0 as Average_20
,0.0 as Unrealised_div_share_21
,0.0 as Unrealised_22
,0.0 as Percent_Change_23
,'' as KeepAt_24
from FIXINCOMESECURITIES_HEADER
       ";


       public static string r3307qry3 = @" 
        select
'' as AssetClass_h1
,'' as AssetType_h2
,'' as Related_h3
,'' as INVOLVED_PARTY_TYPE
,'' as ISSUER_1
,'' as Purchase_Date_2
,0.0 as Unit_Amount_3
,0.0 as Percent_Unit_Per_Total_4
,0.0 as Par_Amount_5
,0.0 as Announce_Booking_Amount_6
,0.0 as Purchase_Amount_7
,0.0 as Book_DuringYear_Amount_8
,0.0 as Book_Appraisal_Amount_9
,0.0 as Book_Net_Amount_10
,0.0 as NAV_Amount_11
,0.0 as Quatation_NAV_Ratio_12
,0.0 as Estimate_Amount_13
,0.0 as Obligation_Amount_14
,0.0 as Asset_Section27_4_15
,'' as Remark_OIC_16
,0.0 as Cost_17
,0.0 as Price_at_reclassified_date_18
,0.0 as Unrealised_Profit_19
,'' as Purchasingdate_20
,0.0 as Cost_per_Unit_21
,'' as Remarks_22
,'' as Type_23
from FIXINCOMESECURITIES_HEADER
       ";

       public static string r3306qry1 = @"
select
		FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID
		,(select REFERENCE_CODE_VALUE_TH from REFERENCE_CODE where REFERENCE_CODE.REFERENCE_CODE_ID = FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID) AS ISSUER_TYPE
from FIXINCOMESECURITIES_HEADER
where FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID is not NULL
group by FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID
";
       public static string r3306qry2 = @"
select
		FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID
		,FIXINCOMESECURITIES_PURCHASE.RELATED 
from FIXINCOMESECURITIES_HEADER
inner join dbo.FIXINCOMESECURITIES_PURCHASE on 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID

where FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID is not NULL
group by FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID ,FIXINCOMESECURITIES_PURCHASE.RELATED
  
    
";
       public static string r3306qry3 = @"


select 
FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID
,FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID
,FIXINCOMESECURITIES_PURCHASE.RELATED
,FIXINCOMESECURITIES_HEADER.SYMBOL
,(select REFERENCE_CODE_VALUE_TH from REFERENCE_CODE where REFERENCE_CODE.REFERENCE_CODE_ID = FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID) AS ISSUER_TYPE
,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME +'('+FIXINCOMESECURITIES_HEADER.SYMBOL+')' AS ISSUER_NAME
,FIXINCOMESECURITIES_HEADER.GUARANTOR_NAME
,FIXINCOMESECURITIES_HEADER.GUARANTOR_TYPE_NAME
,(SELECT ISSUE_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_RATING
,FIXINCOMESECURITIES_HEADER.SYMBOL		
,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE
,FIXINCOMESECURITIES_HEADER.ISSUE_DATE
,FIXINCOMESECURITIES_HEADER.MATURITY_DATE
,FIXINCOMESECURITIES_HEADER.SYMBOL
,FIXINCOMESECURITIES_PURCHASE.unit
,FIXINCOMESECURITIES_PURCHASE.unit / FIXINCOMESECURITIES_HEADER.ISSUE_SIZE AS PERCENT_UNIT
,FIXINCOMESECURITIES_PURCHASE.YIELD
,(FIXINCOMESECURITIES_PURCHASE.unit * FIXINCOMESECURITIES_PURCHASE.PAR) AS Amount_11
,(FIXINCOMESECURITIES_PURCHASE.unit * FIXINCOMESECURITIES_PURCHASE.PAR) AS Amount_12
,FIXINCOMESECURITIES_PURCHASE.GROSS_PRICE AS Amount_13
,0.0 AS AMOUNT_14 
,0.0 AS AMOUNT_15 
,0.0 AS AMOUNT_16 
,0.0 AS AMOUNT_17
,round(FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE,6) AS AMOUNT_18 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE / FIXINCOMESECURITIES_PURCHASE.UNIT AS AMOUNT_19
,0.0 AS AMOUNT_20 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and (select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19') AS AMOUNT_21 

------------------------	
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = '20'
	or (FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO ='24' and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION = 'เงินสำรองประกันภัย')) AS AMOUNT_22 
,ISNULL(
	 (select	
	 sum(FIXINCOMESECURITIES_PURCHASE2.UNIT) 
	from dbo.FIXINCOMESECURITIES_PURCHASE AS FIXINCOMESECURITIES_PURCHASE2 inner join dbo.FIXINCOMESECURITIES_TRANSACTION AS			FIXINCOMESECURITIES_TRANSACTION2 on  FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_PURCHASE_ID = FIXINCOMESECURITIES_TRANSACTION2.PURCHASE_ID
	where 
		FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_HEADER_ID
		and ((select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION2.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)= 'เงินสำรอง')
   
   )
  ,0)
	
+
ISNULL(
((FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE / FIXINCOMESECURITIES_PURCHASE.UNIT) *
 (select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_PURCHASE.KEEP_AT = 'Custodian')) 
	,0) AS AMOUNT_23
	
,FIXINCOMESECURITIES_PURCHASE.REMARK_OIC AS REMARK_OIC
,0.0 AS Unrealised_gains_divide_loss
,0.0 AS Market_Yield
,0.0 AS Cost_Yield
,0.0 AS Modified_duration
,0.0 AS Mkt_value_plus_Mduration
,0.0 AS Par_plus_Market_Yield
,0.0 AS duration_year_from_Settle_date
,0.0 AS duration_year_from_EOM
,0.0 AS Clean_plus_cost_yield
,0.0 AS Mkt_price_plus_Mkt_yield
,0.0 AS Keep_AT	


from FIXINCOMESECURITIES_HEADER
inner join dbo.FIXINCOMESECURITIES_PURCHASE on 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
";


       public static string r3520header = @"
       ";
       public static string r3520detail = @"
select
'' as LoanType_h1
,'' as LoanObject_h2
,0.0 as Loan_bf_Allowance_Amount_2
,0.0 as Interest_bf_Allowance_Amount_3
,0.0 as Loan_bf_Allowance_Total_Amount_4
,0.0 as Loan_af_Allowance_Amount_5
,0.0 as Interest_af_Allowance_Amount_6
,0.0 as Estimate_Loan_Amount_7
,0.0 as Estimate_Interest_Amount_8
,'' as Remark_9
from LOANS_HEADER
        ";



       public static string r3710qry1 = @"";
       public static string r3710qry2 = @"";
       public static string r3710qry3 = @"";
       public static string r3710qry4 = @"



-- 4 level 

select
'' as AssetClass_h1
,'' as AssetType_h2
,'' as PaymentType_h3
,'' as BankCode
,'' as BranchCode
,'' as INVOLVED_PARTY_TYPE
,0.0 as Account_Amount_2
,0.0 as Estimate_Amount_3
,0.0 as Obligation_Amount_4
,0.0 as Asset_Section19_6
,0.0 as Asset_Section27_4_6
,'' as Remarks_7
,'' as BankName_8
,'' as Branch_9
,'' as AccNo_VouchNo_10
,'' as Interest_11
,'' as Term_12
,'' as ValueDate_13
,'' as MaturityDate_14
,'' as Coupon_PaymentDate_15
,'' as Inssue_Rating_16
,'' as Par_Plus_Coupon_17
,'' as Duration_year_from_SettleDate_18
,0.0 as Duration_year_from_EOM_19
,0.0 as Duration_month_from_EOM_20
,0.0 as Duration_day_from_EOM_21
,0.0 as Amount_Plus_Duration_year_22
from DEPOSIT_HEADER
        ";

        //---------------------------------
       public static string r3302qry = @"
select 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
,'' FIXED_INCOME_FULLNAME
,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
,FIXINCOMESECURITIES_HEADER.ISSUER_ID
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME +'('+FIXINCOMESECURITIES_HEADER.SYMBOL+')' AS ISSUER_NAME
,FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_NAME
,FIXINCOMESECURITIES_HEADER.NUMBER_TIME	
,FIXINCOMESECURITIES_HEADER.ISSUE_DATE 
,0.0 AS PHYSICAL_NUMBER
,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE
,FIXINCOMESECURITIES_HEADER.MATURITY_DATE		
,0.0 AS PHYSICAL_AMOUNT
,0.0 AS INTEREST_RATE 
,0.0 AS AMOUNT_7
,FIXINCOMESECURITIES_PURCHASE.PURCHASE_VALUE_TH AS NET_AMOUNT_8
,0.0 AS AMOUNT_9
,0.0 AS AMOUNT_10
,0.0 AS AMOUNT_11
,0.0 AS AMOUNT_12
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE AS AMOUNT_13
,0.0 AS AMOUNT_14 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION = 2) AS AMOUNT_15 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and (select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19') AS AMOUNT_16 

, case ( select 
	top 1  (select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)	
    from 
    FIXINCOMESECURITIES_TRANSACTION
    where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	     
  ) 
  when  'เงินสำรอง' then  
		FIXINCOMESECURITIES_PURCHASE.PAR * 
		ISNULL(
			 (select	
			 sum(FIXINCOMESECURITIES_PURCHASE2.UNIT) 
			from dbo.FIXINCOMESECURITIES_PURCHASE AS FIXINCOMESECURITIES_PURCHASE2 inner join dbo.FIXINCOMESECURITIES_TRANSACTION AS FIXINCOMESECURITIES_TRANSACTION2 on  FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_PURCHASE_ID = FIXINCOMESECURITIES_TRANSACTION2.PURCHASE_ID
			where 
				FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_HEADER_ID
				and ((select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION2.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)= 'เงินสำรอง')
   
		   )
		  ,0)
  else
		case FIXINCOMESECURITIES_PURCHASE.KEEP_AT 
		when 'Custodian' then   
			
			FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE
			*
			(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
				where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
				and 
				(select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19'
		    )
			*
			(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_PURCHASE.KEEP_AT = 'Custodian')
			

		else 0
		end	
  end as AMOUNT_17

,FIXINCOMESECURITIES_PURCHASE.REMARK_OIC	
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME
,'' AS FIXED_INCOME_FULLNAME_TH 
,FIXINCOMESECURITIES_HEADER.SYMBOL
, '' AS Notes
, 0.0 AS Unit
, 0.0 AS Amortize_Interest 
, 0.0 AS Defered_Interest 
, 0.0 AS Unrealised_gains_per_loss
, 0.0 AS Market_Yield
, 0.0 AS Cost_Yield
, 0.0 AS Modified_duration
, getdate() AS Coupon_payment_date
, 0.0 AS Mkt_value_plus_Mduration
, '' AS Tier1
, '' AS Tier2
, '' AS Par_plus_Market_Yield
, '' AS duration_year_from_Settle_date
, '' AS duration_year_from_EOM
, '' AS IRR
, '' AS Clean_plus_cost_yield
, '' AS Mkt_pric_plus_Mkt_yield


,FIXINCOMESECURITIES_HEADER.GUARANTOR_NAME 
,(SELECT RATING_HISTORY.ISSUER_RATING FROM RATING_HISTORY where RATING_HISTORY.FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID 
  and RATING_HISTORY.FINANCIAL_INSTRUMENT_TYPE  = 'ISSUER'
  )as RATING


from FIXINCOMESECURITIES_HEADER
inner join dbo.FIXINCOMESECURITIES_PURCHASE on 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
where 1=1 
";
       public static string r3306qry = @"

select 
FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID
,FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID
,FIXINCOMESECURITIES_PURCHASE.RELATED
,FIXINCOMESECURITIES_HEADER.SYMBOL
,(select REFERENCE_CODE_VALUE_TH from REFERENCE_CODE where REFERENCE_CODE.REFERENCE_CODE_ID = FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID) AS ISSUER_TYPE
,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME +'('+FIXINCOMESECURITIES_HEADER.SYMBOL+')' AS ISSUER_NAME
,FIXINCOMESECURITIES_HEADER.GUARANTOR_NAME
,FIXINCOMESECURITIES_HEADER.GUARANTOR_TYPE_NAME
,(SELECT ISSUE_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUE_RATING
,FIXINCOMESECURITIES_HEADER.SYMBOL		
,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE
,FIXINCOMESECURITIES_HEADER.ISSUE_DATE
,FIXINCOMESECURITIES_HEADER.MATURITY_DATE
,FIXINCOMESECURITIES_HEADER.SYMBOL
,FIXINCOMESECURITIES_PURCHASE.unit
,FIXINCOMESECURITIES_PURCHASE.unit / FIXINCOMESECURITIES_HEADER.ISSUE_SIZE AS PERCENT_UNIT
,FIXINCOMESECURITIES_PURCHASE.YIELD
,(FIXINCOMESECURITIES_PURCHASE.unit * FIXINCOMESECURITIES_PURCHASE.PAR) AS Amount_11
,(FIXINCOMESECURITIES_PURCHASE.unit * FIXINCOMESECURITIES_PURCHASE.PAR) AS Amount_12
,FIXINCOMESECURITIES_PURCHASE.GROSS_PRICE AS Amount_13
,0.0 AS AMOUNT_14 
,0.0 AS AMOUNT_15 
,0.0 AS AMOUNT_16 
,0.0 AS AMOUNT_17
,round(FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE,6) AS AMOUNT_18 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE / FIXINCOMESECURITIES_PURCHASE.UNIT AS AMOUNT_19
,0.0 AS AMOUNT_20 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and (select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19') AS AMOUNT_21 

------------------------	
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = '20'
	or (FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO ='24' and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION = 'เงินสำรองประกันภัย')) AS AMOUNT_22 
,ISNULL(
	 (select	
	 sum(FIXINCOMESECURITIES_PURCHASE2.UNIT) 
	from dbo.FIXINCOMESECURITIES_PURCHASE AS FIXINCOMESECURITIES_PURCHASE2 inner join dbo.FIXINCOMESECURITIES_TRANSACTION AS			FIXINCOMESECURITIES_TRANSACTION2 on  FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_PURCHASE_ID = FIXINCOMESECURITIES_TRANSACTION2.PURCHASE_ID
	where 
		FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_HEADER_ID
		and ((select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION2.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)= 'เงินสำรอง')
   
   )
  ,0)
	
+
ISNULL(
((FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE / FIXINCOMESECURITIES_PURCHASE.UNIT) *
 (select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_PURCHASE.KEEP_AT = 'Custodian')) 
	,0) AS AMOUNT_23
	
,FIXINCOMESECURITIES_PURCHASE.REMARK_OIC AS REMARK_OIC
,0.0 AS Unrealised_gains_divide_loss
,0.0 AS Market_Yield
,0.0 AS Cost_Yield
,0.0 AS Modified_duration
,0.0 AS Mkt_value_plus_Mduration
,0.0 AS Par_plus_Market_Yield
,0.0 AS duration_year_from_Settle_date
,0.0 AS duration_year_from_EOM
,0.0 AS Clean_plus_cost_yield
,0.0 AS Mkt_price_plus_Mkt_yield
,0.0 AS Keep_AT	


from FIXINCOMESECURITIES_HEADER
inner join dbo.FIXINCOMESECURITIES_PURCHASE on 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
where 1=1 
";
     public static  string r3303qry = @"
select 
FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID
,FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID
,FIXINCOMESECURITIES_PURCHASE.RELATED
,FIXINCOMESECURITIES_HEADER.FIXED_INCOME_FULLNAME_TH
,(select REFERENCE_CODE_VALUE_TH from REFERENCE_CODE where REFERENCE_CODE.REFERENCE_CODE_ID = FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID) AS ISSUER_TYPE
,(select INVOLVED_PARTY_TYPE_CODE from INVOLVED_PARTY_TYPE 
		where INVOLVED_PARTY_TYPE.INVOLVED_PARTY_TYPE_ID= FIXINCOMESECURITIES_HEADER.INVOLVED_PARTY_TYPE_ID) AS INVOLVED_PARTY_TYPE_CODE
,FIXINCOMESECURITIES_HEADER.ISSUER_NAME +'('+FIXINCOMESECURITIES_HEADER.SYMBOL+')' AS ISSUER_NAME
,FIXINCOMESECURITIES_HEADER.GUARANTOR_NAME
,(SELECT ISSUER_RATING FROM RATING_HISTORY 
		WHERE RATING_HISTORY_ID = (SELECT MAX(RATING_HISTORY_ID) FROM RATING_HISTORY 
		WHERE FINANCIAL_INSTRUMENT_TYPE = 'FIXINCOMESECURITIES' AND FINANCIAL_INSTRUMENT_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID)) AS ISSUER_RATING
,FIXINCOMESECURITIES_HEADER.SYMBOL		
,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE
,FIXINCOMESECURITIES_HEADER.MATURITY_DATE
,FIXINCOMESECURITIES_PURCHASE.YIELD
,(FIXINCOMESECURITIES_PURCHASE.Unit * FIXINCOMESECURITIES_PURCHASE.PAR) AS Amount_8
,FIXINCOMESECURITIES_PURCHASE.GROSS_PRICE AS GROSS_PRICE
,0.0 AS AMOUNT_9
,0.0 AS AMOUNT_10 
,0.0 AS AMOUNT_11 --default = 0
,0.0 AS AMOUNT_12
,0.0 AS AMOUNT_13 --นำค่ามาจาก Monthly ถ้าถึงหน้า Monthly 0กลับมาอัพเดตที่นี่อีกที
,0.0 AS AMOUNT_14 --นำค่ามาจาก Monthly ถ้าถึงหน้า Monthly 0กลับมาอัพเดตที่นี่อีกที
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_TRANSACTION.OBLIGATION = 2) AS AMOUNT_15 
,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and (select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19') AS AMOUNT_16 
--,FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE*(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
--	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
--	and ((select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)= 'เงินสำรอง')
--	or FIXINCOMESECURITIES_PURCHASE.KEEP_AT = 'Custodian') AS AMOUNT_17
	
	
	, case ( select 
	top 1  (select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)	
    from 
    FIXINCOMESECURITIES_TRANSACTION
    where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	     
  ) 
  when  'เงินสำรอง' then  
		FIXINCOMESECURITIES_PURCHASE.PAR * 
		ISNULL(
			 (select	
			 sum(FIXINCOMESECURITIES_PURCHASE2.UNIT) 
			from dbo.FIXINCOMESECURITIES_PURCHASE AS FIXINCOMESECURITIES_PURCHASE2 inner join dbo.FIXINCOMESECURITIES_TRANSACTION AS FIXINCOMESECURITIES_TRANSACTION2 on  FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_PURCHASE_ID = FIXINCOMESECURITIES_TRANSACTION2.PURCHASE_ID
			where 
				FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE2.FIXINCOMESECURITIES_HEADER_ID
				and ((select OBLIGATION_NAME from OBLIGATION where FIXINCOMESECURITIES_TRANSACTION2.OBLIGATION_NO = OBLIGATION.OBLIGATION_ID)= 'เงินสำรอง')
   
		   )
		  ,0)
  else
		case FIXINCOMESECURITIES_PURCHASE.KEEP_AT 
		when 'Custodian' then   
			------- amount 16
			FIXINCOMESECURITIES_PURCHASE.CLEAN_PRICE
			*
			(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
				where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
				and 
				(select OBLIGATION_NO_NAME from OBLIGATION_NO where FIXINCOMESECURITIES_TRANSACTION.OBLIGATION_NO = OBLIGATION_NO.OBLIGATION_NO_ID)= '19'
		    )
			*
			(select sum(FIXINCOMESECURITIES_TRANSACTION.UNIT) from FIXINCOMESECURITIES_TRANSACTION 
	where FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID
	and FIXINCOMESECURITIES_PURCHASE.KEEP_AT = 'Custodian')
			--- transaction unit

		else 0
		end	
  end as AMOUNT_17

	
	
,FIXINCOMESECURITIES_PURCHASE.REMARK_OIC
,0.0 AS Term
,getdate() AS Coupon_Payment_Date 
,'' AS Issuer_Rating
,0.0 AS Par_Plus_coupon
,0.0 AS duration_year_from_SettleDate
,0.0 AS duration_year_from_EOM
,0.0 AS duration_month_from_EOM
,0.0 AS duration_day_from_EOM
,0.0 AS amount_plus_duration_year

from FIXINCOMESECURITIES_HEADER
inner join dbo.FIXINCOMESECURITIES_PURCHASE on 
FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID

--where (FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Promissory Notes' or FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN ='Bills of exchanges (B/E)')


            ";
    }
}
